﻿namespace LinqToSql
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Entities.Employees;

    /// <summary>
    /// TODO: Update summary.
    /// </summary>
    public static class LinqQueries
    {
        #region Constatns

        private const string connectionString = @"Data Source=JULIA-PC\SQLEXPRESS;Initial Catalog=employees;Integrated Security=True";

        #endregion

        #region Constructors

        static LinqQueries()
        {
            m_db = new EmployeesDataClassesDataContext(connectionString);
        }

        #endregion

        #region Logic

        /// <summary>
        ///  Selects all department's cities
        /// </summary>
        public static void Linq1()
        {
            var cities = from city in m_db.Depts
                         select city.City;

            Console.WriteLine("-1--------------Cities--------------");
            foreach (var item in cities)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Selects list of employee's names without repeats and sorted by alphabet.
        /// </summary>
        public static void Linq2()
        {
            var names = (from name in m_db.Empls
                         orderby name.FirstName
                         select name.FirstName).Distinct();

            Console.WriteLine("-2--------------Names--------------");
            foreach (var item in names)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Calulates amount of employees in certain department
        /// </summary>
        /// <param name="deptId">department's number</param>
        public static void Linq3(int deptId)
        {
            var amount = m_db.Empls.Where(employee => (employee.DeptID == deptId)).Select(name => name.FirstName).Count();

            Console.WriteLine("-3--------------All employees from 3d department--------------");
            Console.WriteLine(amount);
        }

        /// <summary>
        /// Gets list departments with amount of employees. Sorted by amount of employees.
        /// </summary>
        public static void Linq4()
        {
            var IDandAmount = (from employee in m_db.Empls
                               group employee by employee.DeptID into dept
                               orderby dept.Count() descending
                               select new { ID = dept.Key, Amount = dept.Count() }).ToList();

            Console.WriteLine("-4--------------Department ID | People amount in department--------------");
            foreach (var item in IDandAmount)
            {
                Console.WriteLine("{0} | {1}", item.ID, item.Amount);
            }
        }

        /// <summary>
        /// Gets list departments with amount of employees. Sorted by department city.
        /// </summary>
        public static void Linq5()
        {
            var depts = (from employee in m_db.Empls
                         join department in m_db.Depts on employee.DeptID equals department.ID
                         group department by department.City into dept
                         orderby dept.Key
                         select new { City = dept.Key, Amount = dept.Count() }).ToList();

            Console.WriteLine("-5-------------- City | People amount in department--------------");
            foreach (var item in depts)
            {
                Console.WriteLine("{0} | {1} ", item.City, item.Amount);
            }
        }

        /// <summary>
        /// Selects list of cities, which contain leter 'D' at the beginig of the word.
        /// </summary>
        public static void Linq6()
        {
            var cities = m_db.Depts.Where(city => city.City.StartsWith("D")).Select(city => city.City);

            Console.WriteLine("-6--------------Cities started with D--------------");
            foreach (var item in cities)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Selects list of cities, which contain leter 'l' at the middle of the word and amount of employees
        /// </summary>
        public static void Linq7()
        {
            var cities =
                (from department in m_db.Depts
                 join employee in m_db.Empls on department.ID equals employee.DeptID into lj
                 from rows in lj.DefaultIfEmpty()
                 where department.City.IndexOf('l') > 0 && department.City.IndexOf('l') < department.City.Length - 1
                 orderby department.City
                 let EmplID = rows == null ? 0 : 1
                 select new { City = department.City, EmplID }).GroupBy(a => a.City)
                 .Select(item => new { City = item.Key, Amount = item.Sum(a => a.EmplID) })
                 .ToDictionary(dept => dept.City, dept => dept.Amount);

            Console.WriteLine("-7-------------- City with 'l' in the name | People amount in department-------");
            foreach (var item in cities)
            {
                Console.WriteLine("{0} | {1} ", item.Key, item.Value);
            }
        }

        /// <summary>
        /// Selects list of person with the same surname. Sorted by alphabet.
        /// </summary>
        public static void Linq8()
        {
            var namesGroups = (from name in m_db.Empls
                               from name2 in m_db.Empls
                               where name.ID != name2.ID && name.LastName == name2.LastName
                               orderby name.LastName, name.FirstName
                               select new Employee { ID = name.ID, FirstName = name.FirstName, LastName = name.LastName, DeptID = name.DeptID }).Distinct();

            Console.WriteLine("8---------------Same SurNames--------------");
            foreach (var item in namesGroups)
            {
                Console.WriteLine("ID:{0} Name:{1} Surname:{2} DeptID:{3}", item.ID, item.FirstName, item.LastName, item.DeptID);
            }
        }

        /// <summary>
        /// Selects people with thw same name and surname from Lvov. Sorted by name
        /// </summary>
        public static void Linq9()
        {
            var namesGroups = ((from name in m_db.Empls
                                from name2 in m_db.Empls
                                where name.ID != name2.ID && name.FirstName == name2.FirstName && name.LastName == name2.LastName && name.DeptID == name2.DeptID
                                orderby name.FirstName
                                select new Employee { ID = name.ID, FirstName = name.FirstName, LastName = name.LastName, DeptID = name.DeptID }).Where(d => d.DeptID == m_db.Depts.Where(a => a.City == "Lvov").Select(a => new { ID = a.ID }).First().ID)).Distinct();

            Console.WriteLine("-9--------------Same full names--------------");
            foreach (var item in namesGroups)
            {
                Console.WriteLine("ID:{0} Name:{1} Surname:{2} DeptID:{3}", item.ID, item.FirstName, item.LastName, item.DeptID);
            }
        }

        /// <summary>
        /// Selects list of cities with amount of employees named Vasiliy more than 10. Sorted by city
        /// </summary>
        public static void Linq10()
        {
            var depts = (from employee in m_db.Empls
                         join department in m_db.Depts on employee.DeptID equals department.ID
                         where employee.FirstName == "Vasiliy"
                         group department by department.City into dept
                         orderby dept.Key
                         select new { City = dept.Key, Amount = dept.Count() }).Where(d => d.Amount > 10).ToDictionary(dept => dept.City, dept => dept.Amount);


            Console.WriteLine("-10-------------- City | People amount in department (Vasiliy)--------------");
            foreach (var item in depts)
            {
                Console.WriteLine("{0} | {1} ", item.Key, item.Value);
            }
        }

        #endregion

        #region Fields

        private static EmployeesDataClassesDataContext m_db;

        #endregion
    }
}
